How to: Display data from several tables using a join.
Solution:
In Query Design view, add two or more tables to the query grid. Drag-and-drop the desired field name from one table on top of the same field name in a second table to join the tables.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' in the New Query list box.
5) Click 'OK'. (The Show Table dialog box appears.)
6) Select the table to be used in the query.
7) Click 'Add'. (The table appears behind the dialog box.)
8) Repeat step 7 for each additional table to add to the query.
9) Click 'Close'. (The Query design view appears.)
10) To join one or more tables in the query, do the following:
NOTE: The tables to be joined must have the same field name or type created in the table.
EXAMPLE: Join 'OrderID' from one table to 'OrderID' of another table.
a) Drag-and-drop the desired field from the first table on top of the same field in the second table. (A join line appears.)
b) Repeat steps 10)a) for each additional table to be joined.
11) To add fields to the query:
a) Select a field from a table's field list box in the top half of the Query Design window.
b) Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
c) Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
d) Repeat steps 11)a) through 11)c) for each field to be added to the query.
NOTE: Fields can also be added to the query by selecting a field from the drop-down list in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
12) Select the 'File' menu and select 'Save' to save the query.
13) Type the desired name for the query in the 'Save As' box.
14) Click 'OK'. (The selected query is saved.)
